/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package tugasbesarjeni1.Database;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import javax.swing.JOptionPane;

/**
 *
 * @author Indy
 */
public class query {
    private java.sql.Statement statement ;
    private boolean b = false;
    ResultSet resultSet = null;
    public boolean simpanVcd(String kode, String judul,String kategori,String hargasewa){
        try{
            statement = konek.getConnection().createStatement();
            String sql = "insert into table_vcd values ('"+kode+"','"+judul+"','"+kategori+"','"+hargasewa+"');";
            b = statement.execute(sql);
             JOptionPane.showMessageDialog(null, "Berhasil disimpan");
        }catch(SQLException ex){
            JOptionPane.showMessageDialog(null, ex, "Error", JOptionPane.ERROR_MESSAGE);
        }return b;
    }
    public ResultSet selectVcd(){
        try{
            statement = konek.getConnection().createStatement();
            String sql = "Select * from table_vcd order by kode";
            resultSet = statement.executeQuery(sql);
        }catch(SQLException ex){
            JOptionPane.showMessageDialog(null, ex, "Error", JOptionPane.ERROR_MESSAGE);
        }
        return resultSet;
    }
    public boolean updateVcd(String kode, String judul, String kategori, String hargaSewa) {
         try {
            statement = konek.getConnection().createStatement();
            String sql = "UPDATE  vcd SET kode = '" + kode + "',judul = '" + judul + "',kategori = '" + kategori + "',harga_sewa = '" + hargaSewa + "'where kode = '" + kode+ "'";
            b = statement.execute(sql);
            JOptionPane.showMessageDialog(null, "Data berhasil diubah");
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, ex, "Error", JOptionPane.ERROR_MESSAGE);
        }
        return b;
    }
    public ResultSet searchVcd(String cari, String data) {
        try {
            statement = konek.getConnection().createStatement();
            String sql = "select * from table_vcd where "+cari+" like '%"+data+"%'";
            resultSet = statement.executeQuery(sql);
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, ex, "Error", JOptionPane.ERROR_MESSAGE);
        }
        return resultSet;
    }
    public ResultSet searchVcd(String kode) {
        try {
            statement = konek.getConnection().createStatement();
            String sql = "select kode, judul, harga_sewa from table_vcd where kode like '" + kode + "'";
            resultSet = statement.executeQuery(sql);
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, ex, "Error", JOptionPane.ERROR_MESSAGE);
        }
        return resultSet;
    }
    public ResultSet searchHargaVcd(String kode) {
        try {
            statement = konek.getConnection().createStatement();
            String sql = "select harga_sewa from table_vcd where kode = '" + kode + "'";
            resultSet = statement.executeQuery(sql);
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, ex, "Error", JOptionPane.ERROR_MESSAGE);
        }
        return resultSet;
    }
    public boolean hapusVcd(String kode){
        try{
            statement = konek.getConnection().createStatement();
            String sql = "delete from table_vcd where kode = '"+kode+"'";
            b = statement.execute(sql);
        }catch(SQLException ex){
            JOptionPane.showMessageDialog(null, ex, "Error", JOptionPane.ERROR_MESSAGE);
        }return b;
    }
   public boolean  simpanpeminjaman(String no, String nama, String notelp,String alamat,String kode, String tglpinjam, String tglkembali){
       try{
           statement = konek.getConnection().createStatement();
           String sql = "insert into table_peminjaman values ('"+no+"','"+nama+"','"+notelp+"','"+alamat+"','"+kode+"','"+tglpinjam+"','"+tglkembali+"');";
           b = statement.execute(sql);
       }catch(SQLException ex){
           JOptionPane.showMessageDialog(null, ex, "Error", JOptionPane.ERROR_MESSAGE);
       }return  b;
   }
   public ResultSet selectpeminjaman(){
       try{
           statement = konek.getConnection().createStatement();
           String sql = "select no_id,nama from table_peminjaman order by no_id";
           resultSet = statement.executeQuery(sql);
       }catch(SQLException ex){
               JOptionPane.showMessageDialog(null, ex, "Error", JOptionPane.ERROR_MESSAGE);
       }return resultSet;
   }
   public ResultSet searchPeminjaman() {
        try {
            statement = konek.getConnection().createStatement();
            String sql = "select p.no_id, a.nama, p.kode, v.judul, p.tgl_pinjam, p.tgl_kembali " +
                         "from table_peminjaman p, table_vcd v " +
                         "where p.no_id = a.no_id and p.kode = v.kode order by no_id";
            resultSet = statement.executeQuery(sql);
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, ex, "Error", JOptionPane.ERROR_MESSAGE);
        }
        return resultSet;
    }
    public ResultSet selectPeminjaman(String noID) {
        try {
            statement = konek.getConnection().createStatement();
            String sql = "select * from table_peminjaman where no_id = '" + noID + "'";
            resultSet = statement.executeQuery(sql);
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, ex, "Error", JOptionPane.ERROR_MESSAGE);
        }
        return resultSet;
    }
    public ResultSet searchPeminjaman(String noID, String nama) {
        try {
            statement = konek.getConnection().createStatement();
            String sql = "select p.no_id, p.nama, p.kode, v.judul, p.tgl_pinjam, p.tgl_kembali " +
                         "from table_peminjaman p, table_vcd v " +
                         "where p.no_id = '"+noID+"' and p.kode = v.kode and p.nama = '"+nama+"' order by no_id";
            resultSet = statement.executeQuery(sql);
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, ex, "Error", JOptionPane.ERROR_MESSAGE);
        }
        return resultSet;
    }
    public ResultSet searchnamaPeminjaman(String noID) {
        try {
            statement = konek.getConnection().createStatement();
            String sql = "select nama,no_id from table_peminjaman where no_id like '"+noID+"'";
            resultSet = statement.executeQuery(sql);
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, ex, "Error", JOptionPane.ERROR_MESSAGE);
        }
        return resultSet;
    }
    public boolean deletePeminjaman(String noID){
        try {
            statement = konek.getConnection().createStatement();
            String sql = "delete from table_peminjaman where no_id = '" + noID + "'";
            b = statement.execute(sql);
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, ex, "Error", JOptionPane.ERROR_MESSAGE);
        }
        return b;
    }
    public boolean  insertPengembalian(String noID, String kode, String tglPinjam, String tglKembali) {
        try {
            statement = konek.getConnection().createStatement();
            String sql = "insert into table_pengembalian values" +
                        "('"+noID+"','" + kode + "','" + tglPinjam + "','" + tglKembali + "')";
            b = statement.execute(sql);
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, ex, "Error", JOptionPane.ERROR_MESSAGE);
        }
        return b;
    }
    public ResultSet selectTanggal(String noID) {
        try {
            statement = konek.getConnection().createStatement();
            String sql = "select tgl_kembali from table_peminjaman where no_id = '" + noID + "'";
            resultSet = statement.executeQuery(sql);
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, ex, "Error", JOptionPane.ERROR_MESSAGE);
        }
        return resultSet;
    }
    public ResultSet selectSelisihTanggal(String tgl_sekarang, Date tgl_kembali) {
        try {
            statement = konek.getConnection().createStatement();
            String sql = "select datediff('" + tgl_sekarang + "','" + tgl_kembali + "')";
            resultSet = statement.executeQuery(sql);
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, ex, "Error", JOptionPane.ERROR_MESSAGE);
        }
        return resultSet;
    }
}
